/**
 * 
 */
package com.yunhe.tomcat.iservice.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.yunhe.tomcat.iservice.IStudentTurnDao;
import com.yunhe.tomcat.model.Student;
import com.yunhe.tomcat.util.Pool;

/**
 * 学生改动接口实现类
 * 
 * @author 米mi
 *
 */
public class StudentTurnImpl implements IStudentTurnDao {

    /**
     * 引入连接池
     */
    private final Pool pool = new Pool();

    /**
     * 添加学生
     * 
     * @param stu
     *            学生实体
     * @return int 数据库影响行数
     */
    public int saveStudent(Student stu) {

        // 1.使用连接池建立数据库连接
        Connection conn = pool.getConnection();
        PreparedStatement pst = null;
        int record = 0;

        String sql = "insert into t_student(student_name,student_sex,student_age,student_date) values(?,?,?,?)";
        try {
            // 2.创建状态
            pst = conn.prepareStatement(sql);
            pst.setString(1, stu.getStudentName());
            pst.setString(2, stu.getStudentSex());
            pst.setInt(3, stu.getStudentAge());
            pst.setString(4, stu.getStudentDateStr());

            // 3.执行sql语句并返回结果
            record = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 4.断开数据库连接
            pool.releasePreparedStatement(pst);
            // 5.归还数据库连接给连接池
            pool.releaseConnection(conn);
        }

        return record;
    }

    /**
     * 删除学生
     * 
     * @param studentId
     * @return int 数据库影响行数
     */
    public int deleteStudent(int studentId) {

        // 1.使用连接池建立数据库连接
        Connection conn = pool.getConnection();
        PreparedStatement pst = null;
        int record = 0;

        String sql = "update t_student set student_state = 0 where student_state = 1 and student_id = ?";
        try {
            // 2.创建状态
            pst = conn.prepareStatement(sql);
            pst.setInt(1, studentId);

            // 3.执行sql语句并返回结果
            record = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 4.断开数据库连接
            pool.releasePreparedStatement(pst);
            // 5.归还数据库连接给连接池
            pool.releaseConnection(conn);
        }

        return record;
    }

    /**
     * 永久删除学生
     * 
     * @param studentId
     * @return int 数据库影响行数
     */
    public int delStudent(int studentId) {

        // 1.使用连接池建立数据库连接
        Connection conn = pool.getConnection();
        PreparedStatement pst = null;
        int record = 0;

        String sql = "delete from t_student where student_id = ?";
        try {
            // 2.创建状态
            pst = conn.prepareStatement(sql);
            pst.setInt(1, studentId);

            // 3.执行sql语句并返回结果
            record = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 4.断开数据库连接
            pool.releasePreparedStatement(pst);
            // 5.归还数据库连接给连接池
            pool.releaseConnection(conn);
        }

        return record;
    }

    /**
     * 修改学生信息
     * 
     * @param stu
     *            学生实体
     * @return int 数据库影响行数
     */
    public int updateStudent(Student stu) {

        // 1.使用连接池建立数据库连接
        Connection conn = pool.getConnection();
        PreparedStatement pst = null;
        int record = 0;

        String sql = "update t_student set student_name = ?, student_sex = ?, student_age = ?, student_date = ? where student_state = 1 and student_id = ?";
        try {
            // 2.创建状态
            pst = conn.prepareStatement(sql);
            pst.setString(1, stu.getStudentName());
            pst.setString(2, stu.getStudentSex());
            pst.setInt(3, stu.getStudentAge());
            pst.setString(4, stu.getStudentDateStr());
            pst.setInt(5, stu.getStudentId());

            // 3.执行sql语句并返回结果
            record = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 4.断开数据库连接
            pool.releasePreparedStatement(pst);
            // 5.归还数据库连接给连接池
            pool.releaseConnection(conn);
        }

        return record;
    }

    /**
     * 查询所有学生
     * 
     * @return Student 学生实体
     */
    public List<Student> listStident() {

        // 1.使用连接池建立数据库连接
        Connection conn = pool.getConnection();
        Statement st = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<Student>();

        String sql = "select student_id, student_name, student_sex, student_age, student_date from t_student where student_state = 1";
        try {
            // 2.创建状态
            st = conn.createStatement();
            // 3.执行sql语句并返回结果
            rs = st.executeQuery(sql);

            while (rs.next()) {
                Student stu = new Student();
                stu.setStudentId(rs.getInt(1));
                stu.setStudentName(rs.getString(2));
                stu.setStudentSex(rs.getString(3));
                stu.setStudentAge(rs.getInt(4));
                stu.setStudentDate(rs.getTimestamp(5));
                list.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 4.断开数据库连接
            pool.releaseResultSet(rs);
            pool.releaseStatement(st);
            // 5.归还数据库连接给连接池
            pool.releaseConnection(conn);
        }

        return list;
    }

}
